<?php 
require_once ("DB.php");

function service_glossary_create($glossary) {
    $conn = getDBConnection();
    $conn -> query("insert into glossaries(title, abbr, author, editor, version, publisher, year_published, description, source, creator, create_date) value(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
         array($glossary['title'], $glossary['abbr'], $glossary['author'], $glossary['editor'], $glossary['version'],
         $glossary['publisher'], $glossary['year_published'], $glossary['description'], $glossary['source'],
         $glossary['creator'], date('Y-m-d H:i:s', time())));
    $result = $conn -> query("select last_insert_id()");
    if ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row;
    }
    $serial = $set['last_insert_id()'];
    $result -> free();
    $conn -> disconnect();
    return $serial;
}


function service_glossary_getBySerial($serial) {
    $conn = getDBConnection();
    $set = array();
    $result = $conn -> query("select * from glossaries where serial = ? limit 1", array($serial));
    while ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row;
    }
    $result -> free();
    $conn -> disconnect();
    if (count($set) > 0) {
        return $set;
    } else {
        return null;
    }
}


function service_glossary_getByCreator($creator, $orderby = "create_date desc") {
    $conn = getDBConnection();
    $set = array();
    $result = $conn -> query("select * from glossaries where creator = ? order by $orderby", array($creator));
    while ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row);
    }
    $result -> free();
    $conn -> disconnect();
    if (count($set) > 0) {
        return $set;
    } else {
        return null;
    }
}


function service_glossary_getAll($start, $size, $orderBy = "create_date desc") {
    $conn = getDBConnection();
    $result = $conn -> query("select * from glossaries order by ".$orderBy." limit ?,? ", array($start,
         $size));
    $set = array();
    while ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row);
    }
    $result -> free();
    $conn -> disconnect();
    return $set;
}


function service_glossary_getCount() {
    $conn = getDBConnection();
    $result = $conn -> query("select count(serial) from glossaries");
    $set = 0;
    if ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row['count(serial)'];
    }
    $result -> free();
    $conn -> disconnect();
    return $set;
}


function service_glossary_update($glossary) {
    $conn = getDBConnection();
    $conn -> query("update glossaries set title=?,abbr=?,author=?,editor=?,
        version=?,publisher=?,year_published=?,description=?,source=?,
        last_editor=?,last_edit_date=? where serial = ? limit 1", array($glossary['title'], $glossary['abbr'],
             $glossary['author'], $glossary['editor'], $glossary['version'], $glossary['publisher'],
             $glossary['year_published'], $glossary['description'], $glossary['source'], $glossary['last_editor'],
             date('Y-m-d H:i:s', time()), $glossary['serial']));
    $conn -> disconnect();
}


function service_glossary_deleteBySerial($serial) {
    $conn = getDBConnection();
    $conn -> query("delete from glossaries where serial = ? limit 1", array($serial));
    $conn -> disconnect();
}


function service_glossary_addterms($terms, $creator, $glossary) {
    $conn = getDBConnection();
    $num = 0;
    foreach ($terms as $term=>$definition) {
        $conn -> query("insert into glossary_terms(term, definition, creator, create_date, belongto, version) value(?,?,?,?,?,1)",
             array($term, $definition, $creator, date('Y-m-d H:i:s', time()), $glossary));
        $num++;
    }
    $conn -> disconnect();
    return $num;
}

function service_glossary_addterm_detail($attrs, $creator, $glossary) {
    $conn = getDBConnection();
    $conn -> query("insert into glossary_terms(term, definition, 
		word_class,
		national_variants,
		admitted_terms,
		deprecated_terms,
		abbreviation,
		acronym,
		full_forms,
		source_location,
		subject_field,
		broader_term,
		narrower_term,
		synonym,
		name_of_creators,
		notes, 
		creator, create_date, belongto, version) value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1)",
         array($attrs['term'], $attrs['definition'],
		 	$attrs['word_class'],
			$attrs['national_variants'],
			$attrs['admitted_terms'],
			$attrs['deprecated_terms'],
			$attrs['abbreviation'],
			$attrs['acronym'],
			$attrs['full_forms'],
			$attrs['source_location'],
			$attrs['subject_field'],
			$attrs['broader_term'],
			$attrs['narrower_term'],
			$attrs['synonym'],
			$attrs['name_of_creators'],
			$attrs['notes'], 
			$creator, date('Y-m-d H:i:s', time()), $glossary));
    $conn -> disconnect();
    return $num;
}

function service_glossary_getTerm($serial) {
	$conn = getDBConnection();
	$set = array();
	$result = $conn -> query("select * from glossary_terms where serial = ? limit 1",
		$serial);
    while ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row;
    }
    $result -> free();
    $conn -> disconnect();
    if (count($set) > 0) {
        return $set;
    } else {
        return null;
    }
}

function service_glossary_getTerms($serial, $category = "", $start, $size, $orderBy = "term") {
    $conn = getDBConnection();
    
    $variables = array($serial, $start, $size);
    if ($category != "") {
        $categorySyntax = " and lower(substring(term,1,1)) = ?";
        array_splice($variables, 1, 0, $category);
    }
    
    $set = array();
    $result = $conn -> query("select * from glossary_terms where belongto = ?".$categorySyntax." order by ".$orderBy." limit ?,?",
         $variables);
    while ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row);
    }
    $result -> free();
    $conn -> disconnect();
    if (count($set) > 0) {
        return $set;
    } else {
        return null;
    }
}


function service_glossary_getTermsCount($serial, $category = "") {
    $conn = getDBConnection();
    $variables = array($serial);
    if ($category != "") {
        $categorySyntax = " and lower(substring(term,1,1)) = ?";
        array_push($variables, $category);
    }
    $result = $conn -> query("select count(serial) from glossary_terms where belongto = ?".$categorySyntax,
         $variables);
    $set = 0;
    if ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row['count(serial)'];
    }
    $result -> free();
    $conn -> disconnect();
    return $set;
}


function service_glossary_editTermBySerial($serial, $term, $editor) {
    $conn = getDBConnection();
    $conn -> query("update glossary_terms set term=?,
									last_editor=?,
									last_edit_date=?,
									version = version + 1
									where serial = ? limit 1", array($term, $editor, date('Y-m-d H:i:s', time()), $serial));
    $conn -> disconnect();
}


function service_glossary_editTermDefinitionBySerial($serial, $definition, $editor) {
    $conn = getDBConnection();
    $conn -> query("update glossary_terms set definition=?,
									last_editor=?,
									last_edit_date=?,
									version = version + 1
									where serial = ? limit 1", array($definition, $editor, date('Y-m-d H:i:s', time()), $serial));
    $conn -> disconnect();
}

function service_glossary_edit_term($serial, $attrs, $editor) {
	$conn = getDBConnection();
    $conn -> query("update glossary_terms set 
		term = ?,
		definition = ?,
		word_class = ?,
		national_variants = ?,
		admitted_terms = ?,
		deprecated_terms = ?,
		abbreviation = ?,
		acronym = ?,
		full_forms = ?,
		source_location = ?,
		subject_field = ?,
		broader_term = ?,
		narrower_term = ?,
		synonym = ?,
		name_of_creators = ?,
		notes = ?,
		last_editor=?,
		last_edit_date=?,
		version = version + 1
		where serial = ? limit 1", array(
			$attrs['term'], 
			$attrs['definition'],
		 	$attrs['word_class'],
			$attrs['national_variants'],
			$attrs['admitted_terms'],
			$attrs['deprecated_terms'],
			$attrs['abbreviation'],
			$attrs['acronym'],
			$attrs['full_forms'],
			$attrs['source_location'],
			$attrs['subject_field'],
			$attrs['broader_term'],
			$attrs['narrower_term'],
			$attrs['synonym'],
			$attrs['name_of_creators'],
			$attrs['notes'],  
			$editor, date('Y-m-d H:i:s', time()), $serial));
    $conn -> disconnect();
}

function service_glossary_deleteTermBySerial($serial) {
    $conn = getDBConnection();
    $conn -> query("delete from glossary_terms where serial = ? limit 1", array($serial));
    $conn -> disconnect();
}


function service_glossary_term_search($query, $belongto, $start, $size, $orderBy = "term") {
    $conn = getDBConnection();
    $result = $conn -> query("select * from glossary_terms where (term like ? 
												or definition like ? )
												and belongto = ?
												order by ".$orderBy." limit ?,? ", array("%".$query."%", "%".$query."%", $belongto, $start,
												     $size));
    $set = array();
    while ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row);
    }
    
    $countResult = $conn -> query("select count(serial) from glossary_terms where (term like ? 
														or definition like ?) 
														and belongto = ?", array("%".$query."%", "%".$query."%", $belongto));
    $count = 0;
    if ($row = &$countResult -> fetchRow(DB_FETCHMODE_ASSOC)) {
        $count = $row['count(serial)'];
    }
    $result -> free();
    $conn -> disconnect();
    return array("terms"=>$set, "count"=>$count);
}


function service_glossary_term_exist($source, $term) {
    $conn = getDBConnection();
    $result = $conn -> query("select * from glossary_terms where belongto = ? and term = ? limit 1",
         array($source, $term));
    $conn -> disconnect();
    if ($row = &$result -> fetchRow(DB_FETCHMODE_ASSOC)) {
        $result -> free();
        return TRUE;
    } else
        return FALSE;
}
